#!D:\Python27\python.exe
# -*- coding: utf-8 -*-
'''
    Provides data access class and encapsulates python mysql library

    :copyright: (c) 2014 by Duxin.
'''

import pickle
import datetime

import MySQLdb

import config

class DBManager(object):
    '''
        Encapsulates mysql database access. Database connection is established only once when 
        DBManager object is created and will be released when the object is freed.
        Immunization against SQL injection attacks.
    '''
    
    db = None
    
    @staticmethod
    def get_instance():
        if DBManager.db is None:
            DBManager.db = DBManager()
        
        return DBManager.db

    def __init__(self):
        self.conn = MySQLdb.connect(host=config.db.host,
                user=config.db.username,
                passwd=config.db.password,
                db=config.db.db) 

    def fetch(self, sql, args=None):
        cursor = self.conn.cursor()
        
        if args is None:
            cursor.execute(sql)
        else:
            cursor.execute (sql, args)
        
        row = cursor.fetchone ()
        cursor.close() 
        return row
    
    def execute(self, sql,  args=None):
        cursor = self.conn.cursor()
        
        if args is None:
            cursor.execute(sql)
        else:
            cursor.execute (sql, args)
            
        cursor.close() 
        return True
    
    def commit(self):
        self.conn.commit()
    
    def insert_id(self):
        return self.conn.insert_id()
    
    def __del__():
        self.conn.close()

class User(object):
    def __init__(self):
        self.id = 0
        self.username = ''
        self.password = ''
        self.avatars = [None]   #every element in this array stores a avatar
        self.avatars[0] = None
    
    def get_avatar_url(self):
        return "./yagra/avatar.py?username=%s"%(self.username)

class UserDAO(object):
    @staticmethod
    def find_by_username(username):
        user = User()
        db = DBManager.get_instance()
        
        user_data = db.fetch("select id,password,avatar from user where username=%s", (username))
        
        if user_data is None:
            return None
        else:
            user.id = user_data[0]
            user.username = username
            user.password = user_data[1]
            user.avatars[0] = user_data[2]
            return user
    
    @staticmethod
    def persist(user):
        db = DBManager.get_instance()
        
        if user.id == 0:
            if user.avatars[0] is None:
                db.execute("insert into user (username, password, avatar) values (%s,%s,null) \
            ", (user.username, user.password))
            else:
                db.execute("insert into user (username, password, avatar) values (%s,%s,%s) \
            ", (user.username, user.password, user.avatars[0]))
            user.id = db.insert_id()
        else:
            if user.avatars[0] is None:
                db.execute("update user set username=%s,password=%s where id=%s\
            ", (user.username, user.password, user.id))
            else:
                db.execute("update user set username=%s,password=%s,avatar=%s where id=%s\
            ", (user.username, user.password, user.avatars[0], user.id))
        db.commit()
        return True

class SessionDAO(object):
    @staticmethod
    def find_by_session_id(session_id):
        now = datetime.datetime.now()  
        
        sql = "select value,time from sessions where `id`=%s and unix_timestamp(`time`)+%s > \
        unix_timestamp(now())"%("%s", config.session.expire_time)
        
        db = DBManager.get_instance()
        
        value = db.fetch(sql, [session_id])

        if value and value[0]:
            try:
                session = pickle.loads(value[0])
            except Exception as e:
                session = None
        else:
            session = None
        
        return session
    
    @staticmethod
    def persist(session):
        now = datetime.datetime.now()  
        
        db = DBManager.get_instance()
         
        if SessionDAO.find_by_session_id(session.get_session_id()) is None:
            db.execute("insert into sessions (id, value, time) values (%s,%s,%s) \
            ", [session.get_session_id(), pickle.dumps(session), now])
            db.commit()
        else:
            db.execute("update sessions set value=%s, time=%s where id=%s\
            ", [pickle.dumps(session), now, session.get_session_id()])
            db.commit()
